package com.erpoa.mapper;

import com.erpoa.dto.WareHouseMaterialDto;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;

/**
 * @author chenjimin
 * @Title: WareHouseMaterialMapper
 * @ProjectName oaerp
 * @Description: TODO
 * @date 2020/9/1513:03
 */
@Mapper
public interface WareHouseMaterialMapper {

    @Select("select distinct tc_pgen04 tc_pgen04 from ds2.tc_pgen_file where tc_pgen01 = #{sCode}")
    public String selectpgenByHrNo(@Param("sCode") String sCode);

    /**
     * @param permission
     * @param machine
     * @param sCode
     * @return
     */
    List<WareHouseMaterialDto> selectOrderplanByDataPermisoon(@Param("permission") String permission, @Param("machine") String machine, @Param("sCode") String sCode);

    @Select("select count(*) from dsx5.mg_recmat where FMACHINECODE = #{barcode}")
    int countMachNum(@Param("barcode") String barcode);

    @Select("select FMATCODE from dsx5.mg_processbarcode where fbarcode = #{barcode}")
    String selectFmatCodeByBarCode(@Param("barcode") String barcode);

    @Select("select count(0) from dsx5.MG_Mesfunctional  where " +
            "FFACTORYCODE = #{fFactoryCode} and FWORKSHOPCODE = #{fWorkShopCode} " +
            " and PROGRAMNAME = '物料交接' and FENABLE='Y'")
    int countMesfunctionalByParam(@Param("fFactoryCode") String fFactoryCode, @Param("fWorkShopCode") String fWorkShopCode);

    @Select("SELECT FDATASOURCE FROM MG_USERDATASOURCE WHERE FORGPERSONCODE = #{sCode} ")
    String queryDataSource(@Param("sCode") String sCode);

    @Select("select count(0) from dsx5.mg_recmat where fid =#{fPlanID}")
    int countRecmat(@Param("fPlanID") String fPlanID);

    @Select("select nvl(sum(frspqty),0) from dsx5.mg_recmatdetail where fparentid=#{fPlanID}")
    BigDecimal selectFrspqtyByplanid(@Param("fPlanID") String fPlanID);


    //TODO CJM IMG04' '
    @Select(" select distinct nvl(img10,0) from  ${dataSource}.img_file " +
            " where IMG01=#{fMatCode} and IMG02 =#{fWareCode} and IMG03=#{fSeatCode} and IMG04=' '")
    double selectErpStock(@Param("dataSource") String dataSource, @Param("fMatCode") String fMatCode,
                          @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode);

    @Select(" select nvl(sum(case when tc_x505 = 1 then tc_x509 when tc_x505 = 2  " +
            " then tc_x509*-1 end),0)  from dsx5.tc_x5_file " +
            " where tc_x504 = #{fMatCode} and tc_x506 = #{wareCode} and tc_x507 = #{fSeatCode} ")
    double selectXfSumByParam(@Param("fMatCode") String fMatCode, @Param("wareCode") String wareCode, @Param("fSeatCode") String fSeatCode);

    @Select("select nvl(sum(case when foptype = '领料' then frspqty when  foptype = '退料' then frspqty*-1 end),0)" +
            " from dsx5.mg_recmatdetail where FMATCODE=#{fMatCode} and FWARECODE =#{fWareCode} and  FSEATCODE =#{fSeatCode} and FISHANDOVER ='N'  " +
            "and FRSPTIME > add_months(sysdate,-2)")
    double countRecMateHisQtyByParms(@Param("fMatCode") String fMatCode, @Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode);

    @Select("select count(0) from ${dataSource}.sfb_file where sfb01 = #{fOrderNo} and sfb04='8'")
    int countsfbFlagByOrderNo(@Param("dataSource") String dataSource, @Param("fOrderNo") String fOrderNo);

    @Select(" select distinct fwarename FWARENAME,fseatname FSEATNAME from vw_mg_seat where fwarecode = #{fWareCode} and fseatcode = #{fSeatCode}")
    HashMap selectSeatByParam(@Param("fWareCode") String fWareCode, @Param("fSeatCode") String fSeatCode);


    @Select("select nvl(sfa05,0)  from ${dataSource}.sfa_file where sfa01=#{fOrderNo} and sfa03=#{fMatCode} and sfa08=#{fProcessCode} ")
    double selectsfaByParam(@Param("dataSource") String dataSource, @Param("fOrderNo") String fOrderNo, @Param("fMatCode") String fMatCode, @Param("fProcessCode") String fProcessCode);

    @Select("select nvl(sum(case when foptype = '领料'  then frspqty when  foptype = '退料'  then frspqty*-1 end),0)  from dsx5.mg_recmatdetail " +
            "where forderno =#{fOrderNo} and fmatcode=#{fMatCode} and fprocss=#{fProcessCode} ")
    double selectmgRdByParam(@Param("fOrderNo") String fOrderNo, @Param("fMatCode") String fMatCode, @Param("fProcessCode") String fProcessCode);

    @Select("Select nvl(sfa05-sfa06,0) as fqty from ${dataSource}.sfa_file where sfa01 =#{fOrderNo} and   sfa03 =#{fMatCode} and sfa08 = #{fProcessCode} ")
    double selectSfa2ByParam(@Param("dataSource") String dataSource, @Param("fOrderNo") String fOrderNo, @Param("fMatCode") String fMatCode, @Param("fProcessCode") String fProcessCode);

    @Select(" select count(0) from dsx5.MG_Mesfunctional where FFACTORYCODE = #{fFactoryCode} and " +
            "FWORKSHOPCODE = #{fWorkShopCode} and PROGRAMNAME = '超领' and FENABLE='Y'")
    int countMesFunByParam(@Param("fFactoryCode") String fFactoryCode, @Param("fWorkShopCode") String fWorkShopCode);

    @Insert("insert into mg_recmatdetail (fid, version, fplanid, fmachinecode, forderno, fmatcode, fmatname, foptype, frqsper, frqstime, frqsqty, " +
            "frspqty, frspper, frsptime,  fwarecode, fwarename, fsets, freason, fseatcode, fseatname, funit, " +
            "ffactorycode, ffactoryname, fworkshopcode, fworkshopname, fprocss, fparentid,FISHANDOVER,fCode ,fseqcode) " +
            " select #{uuid}, 0, fplanid, fmachinecode, forderno, fmatcode, fmatname, foptype, frqsper, frqstime, frqsqty, round(#{fWeight},3)," +
            " #{sCode}, sysdate,  #{fWareCode}, #{fWareName}, fsets, freason, #{SeatCode}, #{fSeatName}, funit,   ffactorycode, ffactoryname, fworkshopcode, " +
            "fworkshopname, fprocss, fid ,'Y',fCode,fseqcode   from mg_recmat where fID = #{fPlanID}")
    int insertRdByParam(@Param("uuid") String uuid, @Param("fWeight") double fWeight, @Param("sCode") String sCode, @Param("fWareCode") String fWareCode,
                        @Param("fWareName") String fWareName, @Param("SeatCode") String SeatCode, @Param("fSeatName") String fSeatName, @Param("fPlanID") String fPlanID);

    @Update(" update dsx5.mg_recmat set fRspQty = nvl(fRspQty,0) + #{fWeight} , fRspPer = #{sCode}, fRspTime = sysdate, fWareCode = #{fWareCode}," +
            " fWareName = #{fWareName},fSeatCode = #{fSeatCode},fSeatName = #{fSeatName} where fid = #{fPlanID} ")
    int updateRdMatByParam(@Param("fWeight") double fWeight, @Param("sCode") String sCode, @Param("fWareCode") String fWareCode,
                           @Param("fWareName") String fWareName, @Param("fSeatCode") String fSeatCode, @Param("fSeatName") String fSeatName, @Param("fPlanID") String fPlanID);

    @Update(" update MG_RecMatDetail set fHandOverPer=#{jobnum},fHandOverDTime=sysdate,fIsHandOver='Y' where fid = #{uuid} ")
    int updateRmdByParam(@Param("jobnum") String jobnum, @Param("uuid") String uuid);

    @Select("select count(tc_x5id) from dsx5.tc_x5_file where tc_x5id = #{uuid}")
    int countX5IdbYUUID(@Param("uuid") String uuid);

    @Insert("insert into TC_X5_FILE  (tc_x5id,tc_x518,tc_x501,tc_x514,tc_x505,tc_x509,tc_x511,tc_x510," +
            " tc_x506,tc_x507,tc_x503,tc_x504,tc_x515,tc_x502,tc_x516,tc_x521,TC_X508,tc_x522) " +
            " select #{uuid},0,ffactorycode,fmachinecode,decode(fOpType,'领料',1,'退料',2,'超领',4,'超退',5,'洗机领料'," +
            "6,'洗机退料',7),#{fWeight},#{sCode},sysdate,  #{fWareCode},#{fSeatCode},fOrderNo,fMatCode,fReason,fWorkShopCode,fProcss,fCode,' '" +
            ",fseqcode  from dsx5.mg_recmatdetail where fID = #{uuid}")
    int insertXfByParam(@Param("uuid") String uuid, @Param("fWeight") Double fWeight, @Param("sCode") String sCode, @Param("fWareCode") String fWareCode,
                        @Param("fSeatCode") String fSeatCode);
}
